﻿/*
作用:		移动分类 Article_MoveClass
创建人: 		mr.wang
declare @str nvarchar(100)
exec  Article_MoveClass  1,2, @str output
print(@str)
Article_MoveClass  1,2
*/
	CREATE  procedure  Article_MoveClass
	(
	@ParentId int=0,
	@ClassID int=0,
	@ErrInfo nvarchar(200)='' output
	)
	As 
	
	declare @oldParent int--原来分类的父级ID
	declare @oldclassid int--原来分类的ID
	declare @oldrootid int --原来根栏目ID
	declare @oldParentPath nvarchar(100)--原来栏目路径
	declare @Depth int
	declare @Child int
	declare @PrevID int 
	declare @NextID int
	declare @PrevOrderID int 
	declare @NewParentID int
	declare @iParentID int
	declare @iParentPath nvarchar(200)
	
	declare @mParentPath nvarchar(100)
	
	declare @ClassCount int 
	
	
	
	if not exists(Select * from ArticleClass Where ClassID=@ClassID)
	begin
		Set @ErrInfo='找不到指定的栏目！'  
		return 1	
	end
	else
	begin
		Select @oldParent=ParentID,@oldclassid=ClassID,@oldrootid=rootid,@oldParentPath=ParentPath,@Depth=Depth,@Child=Child,@PrevID=PrevID,@NextID=NextID
		from ArticleClass 
		Where ClassID=@ClassID
	end
	

	
	if @oldParent<>@ParentId --更改了所属栏目，则要做一系列检查
	begin
		if @ParentId=@oldclassid 
		begin 
			Set @ErrInfo='所属栏目不能为自己！'  
			return 1			
		end	


		--判断所指定的栏目是否为外部栏目或本栏目的下属栏目
		if  @oldParent=0
		begin
			if @ParentId>0
			begin
				if not exists(Select rootid From ArticleClass where LinkUrl='' and ClassID=@ParentId)
				begin
					Set @ErrInfo='不能指定外部栏目为所属栏目'  
					return 1			
				end 
				else
				begin
					declare @currrootid int 		
					Select @currrootid=rootid From ArticleClass where LinkUrl='' and ClassID=@ParentId
					if @oldrootid=@currrootid
					begin 
					Set @ErrInfo='不能指定该栏目的下属栏目作为所属栏目'  
					return 1							
					end
		
				end
			end
		end 
		else
		begin 
			if exists(select ClassID From ArticleClass where ParentPath like ''+@oldParentPath+','+Convert(nvarchar(100),@oldclassid)+'%' and classid=@ParentId)	
			begin
					Set @ErrInfo='不能指定该栏目的下属栏目作为所属栏目'  
					return 1				
			end
		
		
		end
	end 
	
	if @oldParent=0
	begin
		set @NewParentID=@oldclassid
		set @iParentID=0
	end
	else
	begin
		set @NewParentID=@oldParent
		set @iParentID=@oldParent
	end	
	  --假如更改了所属栏目
	  --需要更新其原来所属栏目信息，包括深度、父级ID、栏目数、排序、继承版主等数据
	  --需要更新当前所属栏目信息
	declare @maxRootid int 

	select @maxRootid=isnull(max(rootid),0) From ArticleClass

	if @NewParentID<>@ParentId and  not(@iParentID=0 and @ParentId=0)
	begin

		if @PrevID>0 
		begin
			update ArticleClass Set NextID=@NextID Where ClassID=@PrevID
		end
		if @NextID>0 
		begin
			update ArticleClass Set PrevID=@PrevID Where ClassID=@NextID
		end
		
		print('@iParentID的值是:'+Convert(nvarchar(100),@iParentID))
		print('@ParentId的值是:'+Convert(nvarchar(100),@ParentId))
		--return
		
		if  @iParentID>0 and @ParentId=0   --如果原来不是一级分类改成一级分类
		begin
			
			Select @PrevID=ClassID  From ArticleClass where RootID=@maxRootid  and Depth=0
			update ArticleClass set NextID=@ClassID where RootID=@maxRootid  and Depth=0
			set @maxRootid=@maxRootid+1

			

			--更新当前栏目数据
			update ArticleClass set depth=0,OrderID=0,rootid=@maxRootid,parentid=0,ParentPath='0',PrevID=@PrevID,NextID=0 
			where  ClassID=@ClassID
			--如果有下属栏目，则更新其下属栏目数据。下属栏目的排序不需考虑，只需更新下属栏目深度和一级排序ID(rootid)数据
			if @Child>0 
			begin
				declare @i int
				set @i=0
				set @oldParentPath=@oldParentPath+','	
	
				--print('@oldParentPath的值是:'+Convert(nvarchar(100),@oldParentPath))
				--print('Select ParentPath,classid From ArticleClass   where ParentPath like ''%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%''')
				
				--return
				
				declare @CurParentPath nvarchar(100)
				declare @CurClassid int 

				Declare   Cur  CURSOR FOR
					Select ParentPath,classid From ArticleClass   where ParentPath like '%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%'
				Open   Cur 
				Fetch   Cur   Into  @CurParentPath,@CurClassid
				While   @@FETCH_STATUS=0   
				begin
					set @i=@i+1
					set @mParentPath=replace(@CurParentPath,@oldParentPath,'')
					
					update ArticleClass Set depth=depth-@depth,rootid=@maxRootid,ParentPath=@mParentPath where classid=@CurClassid
					Fetch   Cur   Into  @CurParentPath,@CurClassid
				end	
				Close   Cur   
				deallocate  Cur
			end
			update ArticleClass set child=child-1 where ClassID=@iParentID
	
		end
		else if @iParentID>0 and @ParentId>0  --如果是将一个分栏目移动到其他分栏目下
		begin
			--得到当前栏目的下属子栏目数
			
			set @oldParentPath=@oldParentPath+','	
	 
			Select 	@ClassCount=isnull(Count(classid),1) From ArticleClass Where ParentPath like '%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%'	
			--获得目标栏目的相关信息	

			

			declare @Childtmp int 
			declare @ClassIdtmp int
			declare @OrderIDtmp int 
			declare @ParentPathtmp nvarchar(200)
			declare @rootidtmp int
			declare @depthtmp int
	
			Select @Childtmp=Child,@ClassIdtmp=ClassID,@OrderIDtmp=OrderID,@ParentPathtmp=ParentPath,@rootidtmp=rootid,@depthtmp=depth 
			from ArticleClass where Classid=@ParentId

			


			if @Childtmp>0
			begin	
				--得到与本栏目同级的最后一个栏目的OrderID
				Select @PrevOrderID=Max(OrderID) From ArticleClass Where ParentID=@ClassIdtmp
				--得到与本栏目同级的最后一个栏目的ClassID
				Select @PrevID=Classid  From ArticleClass Where ParentID=@ClassIdtmp and OrderID=@PrevOrderID
				Update ArticleClass Set  NextID=@ClassID  Where  ParentID=@ClassIdtmp and OrderID=@PrevOrderID
				--得到同一父栏目但比本栏目级数大的子栏目的最大OrderID，如果比前一个值大，则改用这个值。
				declare @MaxOrderIDtmp int 
				if exists( Select Max(OrderID)  from ArticleClass Where ParentPath like ''+@ParentPathtmp+','+Convert(nvarchar(100),@ClassIdtmp)+',%')
				begin
					Select @MaxOrderIDtmp= Max(OrderID)  from ArticleClass Where ParentPath like ''+@ParentPathtmp+','+Convert(nvarchar(100),@ClassIdtmp)+',%'
					if @MaxOrderIDtmp  is not null
					begin
						if @MaxOrderIDtmp>@PrevOrderID
						begin 
							set @PrevOrderID=@MaxOrderIDtmp
						end 
					end 
				end 
			end 
			else
			begin
				set @PrevID=0
				set @PrevOrderID=@OrderIDtmp
			end 
			/*
			print('@PrevID:'+Convert(nvarchar(200),@PrevID))
			print('@PrevOrderID:'+Convert(nvarchar(200),@PrevOrderID))
			print('@OrderIDtmp:'+Convert(nvarchar(200),@OrderIDtmp))
			print('@ClassCount:'+Convert(nvarchar(200),@ClassCount))

			print(@ParentPathtmp+','+Convert(nvarchar(100),@ClassIdtmp))
			print('Update ArticleClass Set OrderID=OrderID+1 where rootid='+Convert(nvarchar(200),@rootidtmp)+' and OrderID>'+Convert(nvarchar(200),@PrevOrderID))
			return
			*/


			--在获得移动过来的栏目数后更新排序在指定栏目之后的栏目排序数据
			Update ArticleClass Set OrderID=OrderID+@ClassCount+1 where rootid=@rootidtmp and OrderID>@PrevOrderID
			--更新当前栏目数据
			Update ArticleClass Set depth=@depthtmp+1,OrderID=@PrevOrderID+1, rootid=@rootidtmp,ParentID=@ParentId,ParentPath=@ParentPathtmp+','+Convert(nvarchar(100),@ClassIdtmp),PrevID=@PrevID,NextID=0 Where classid=@ClassID
			--如果有子栏目则更新子栏目数据，深度为原来的相对深度加上当前所属栏目的深度
			declare @i1 int 	
				set @i1=0
		 	declare @CurParentPath1 nvarchar(200)
			declare @CurClassid1 int 
			Declare   Cur1  Cursor SCROLL  For
				Select ParentPath,ClassID From ArticleClass   where ParentPath like '%'+@oldParentPath+Convert(nvarchar(100),@ClassID)+'%' order by OrderID
			Open   Cur1 
			Fetch   Cur1   Into @CurParentPath1,@CurClassid1
			While   @@FETCH_STATUS=0   
			begin
				set @i1=@i1+1
				set @iParentPath=@ParentPathtmp+','+Convert(nvarchar(100),@ClassIdtmp)+','+replace(@CurParentPath1,@oldParentPath,'')
				--print('@iParentPath的值为'+@iParentPath)
				update ArticleClass 
				Set depth=depth-@depth+@depthtmp+1,OrderId=@PrevOrderID+@i1,rootid=@rootidtmp,ParentPath=@iParentPath 
				where classid=@CurClassid1
				Fetch   Cur1   Into @CurParentPath1,@CurClassid1
			end	
			Close   Cur1   
			Deallocate   cur1
			--更新所指向的上级栏目的子栏目数
			update ArticleClass set child=child+1 where ClassID=@ParentId
			update ArticleClass set child=child-1 where ClassID=@iParentID
		end 
		else --如果原来是一级栏目改成其他栏目的下属栏目
		begin
			--得到移动的栏目总数
			Select @ClassCount=count(Classid) From ArticleClass Where rootid=@oldrootid	
		


			--获得目标栏目的相关信息	
			declare @Childtmp2 int 
			declare @ClassIdtmp2 int 
			declare @ParentPathtmp2 nvarchar(200)
			declare @OrderIDtmp2 int 
			declare @rootidtmp2 int
			declare @parentidtmp2 int
			declare @depthtmp2 int 

			select @Childtmp2=Child,@ClassIdtmp2=classid,@ParentPathtmp2=ParentPath,@rootidtmp2=rootid,@parentidtmp2=parentId,@depthtmp2=depth,@OrderIDtmp2=OrderID
			From ArticleClass where ClassID=@ParentId




			if @Childtmp2>0 
			begin
				--得到与本栏目同级的最后一个栏目的OrderID
				select  @PrevOrderID=isnull(Max(OrderID),0) From ArticleClass where ParentID=@ClassIdtmp2
				Select @PrevID=isnull(ClassID,0) From ArticleClass Where ParentID=@ClassIdtmp2 and OrderID=@PrevOrderID
				update ArticleClass Set NextID=@ClassID where ParentID=@ClassIdtmp2 and OrderID=@PrevOrderID
				--得到同一父栏目但比本栏目级数大的子栏目的最大OrderID，如果比前一个值大，则改用这个值。
				declare @MaxOrderIDtmp2 int 
				if exists( Select Max(OrderID)  from ArticleClass Where ParentPath like ''+@ParentPathtmp2+','+Convert(nvarchar(100),@ClassIdtmp2)+',%')
				begin
					Select @MaxOrderIDtmp2= Max(OrderID) from ArticleClass Where ParentPath like ''+@ParentPathtmp2+','+Convert(nvarchar(100),@ClassIdtmp2)+',%'
					if @MaxOrderIDtmp2  is not null
					begin
						if @MaxOrderIDtmp2>@PrevOrderID
						begin 
							set @PrevOrderID=@MaxOrderIDtmp2
						end 
					end 
				end 
	
			end 
			else
			begin
				set @PrevID=0
				set @PrevOrderID=@OrderIDtmp2				
			end

			--在获得移动过来的栏目数后更新排序在指定栏目之后的栏目排序数据
			Update ArticleClass Set OrderID=OrderID+ @ClassCount+1 where  rootid=@rootidtmp2 and OrderID>@PrevOrderID
			Update ArticleClass Set PrevID=@PrevID,NextID=0 where ClassID=@ClassID
				
			declare @i2 int 	
			Set @i2=0

			declare @parentidtmp21 int 
			declare @ClassIdtmp21 int 
			declare @depthtmp21 int 
			declare @rootidtmp21 int 
			declare @ParentPathtmp21 nvarchar(200)

			Declare   Cur2  Cursor SCROLL  For
				Select parentid,Classid,depth,rootid,ParentPath
				From ArticleClass   where rootid=@oldrootid  order by OrderID
			Open   Cur2 
			Fetch   Cur2   Into @parentidtmp21,@ClassIdtmp21,@depthtmp21,@rootidtmp21,@ParentPathtmp21
			While   @@FETCH_STATUS=0   
			begin
				Set @i2=@i2+1

				if @parentidtmp21=0 
				begin
					Set @oldParentPath=Convert(nvarchar(200),@ParentPathtmp2)+','+Convert(nvarchar(200),@ClassIdtmp2)
	
					Update ArticleClass Set depth=depth+@depthtmp2+1,OrderId=@PrevOrderID+@i2,rootid=@rootidtmp2,ParentPath=@oldParentPath,parentid=@ParentId  where Classid=@ClassIdtmp21
					
				end 	
				else
				begin
					Set @oldParentPath=Convert(nvarchar(200),@ParentPathtmp2)+','+Convert(nvarchar(100),@ClassIdtmp2)+','+replace(@ParentPathtmp21,'0,','')
					update ArticleClass set depth=depth+@depthtmp2+1,OrderID=@PrevOrderID+@i2,rootid=@rootidtmp2,ParentPath=@oldParentPath where Classid=@ClassIdtmp21
				end 
				Fetch   Cur2   Into @parentidtmp21,@ClassIdtmp21,@depthtmp21,@rootidtmp21,@ParentPathtmp21
			end	
			Close   Cur2  
			Deallocate   Cur2
	
			update ArticleClass set child=child+1 where ClassID=@ParentId
			
		end
	end
GO
